﻿<cfsilent>
	<cfsetting requesttimeout="300" />
	<cfflush interval="1024" />
	<cfscript>
		
		temFile = GetTempDirectory() & createUUID() & ".xls";

		studentUpdate = 0;
		studentInsert = 0;
		
		targetArgs = structNew();
		structInsert(targetArgs, "TabID", "labStudentDebt", true);
		
		studentAdvice = getProperty("serviceFactory").getBean("studentPropertyAdvice");
		datetimeAdvice = getProperty("serviceFactory").getBean("datetimeAdvice");
		
		chsFactory = getProperty("serviceFactory").getBean("chineseFactory");
		
	</cfscript>
</cfsilent>
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>导入欠费学生数据</title>
<link href="<cfoutput>#getProperty('cdnServer')#</cfoutput><cfoutput>#getContextRoot()#</cfoutput>/acadmicRes/assets/css/impoter.css" rel="stylesheet" type="text/css">
</head>

<body>

	<cfif isDefined("FORM.upload")>
		
		<!--- 将文件保存至临时目录 --->
		<cftry>
			<cffile action="upload" fileField="upload" destination="#temFile#" nameconflict="overwrite">
			<cfcatch type="application">
				<p class="error">上传的文件类型不正确, 请使用电子表格作为数据导入文件.</p>
			</cfcatch>
		</cftry>
		
		<!--- 将文件转换为电子表格对象 --->
		<cfif fileExists(temFile)>
			<cftry>
				<cfspreadsheet action="read" src="#temFile#" excludeHeaderRow="true" headerrow="1" query="rs" sheetname="学生信息">
				<cfcatch type="application">
					<p class="error">电子表格文件格式不正确, 请使用下载的模板文件录入学籍信息</p>
				</cfcatch>
			</cftry>
			
			<!--- 文件已成功转换为查询对象 --->
			<cfif isDefined("rs") and isQuery(rs)>

				<p class="success">尝试读取电子表格数据 ... 完成</p>
				
				<!--- 检查必要数据字段 --->
				<cfset columnChecked = true />
				<cfif not listFindNoCase(rs.columnList, "学号")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "姓名")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "欠费状态")><cfset columnChecked = false /></cfif>
				
				<cfif not columnChecked>
					<p class="error">电子表格数据列不完整, 请检查模板文件是否包含以下必须字段信息: 学号,姓名,欠费状态</p>
				</cfif>

				<cfif columnChecked>

					<p class="success">正在检查数据子项 ... 完成</p> 
				
					<cftransaction>
					
						<cfloop query="rs">
						
							<!--- 初始化状态属性 --->
							<cfset propAdvice = studentAdvice.init() />
							
							<!--- 加载学生档案 --->
							
                            <cfset sql = "SELECT a.stu_name,
												   b.student_prop
											FROM t_student a 
												 INNER JOIN t_student_status b ON b.stu_id = a.stu_id
											WHERE a.stu_id = :DirID "/>
                                            
							<cfset queryObj = new query( datasource=application.dnsMaster)/>
                            <cfset queryObj.addParam( name="DirID", value=rs["学号"][rs.currentRow] , cfsqltype="cf_sql_varchar" )/>
                            <cfset rs_student = queryObj.execute( sql=sql ).getResult()/>
						
							<cfif rs_student.recordCount GT 0>
								
								<!--- 检查学号与姓名信息匹配 --->
								<cfif rs_student.stu_name eq rs['姓名'][rs.currentRow] >

										<cfset propAdvice.parseProperty( rs_student.student_prop ) />
									
										<cfif  rs["欠费状态"][rs.currentRow] eq "欠费">
												<cfset propAdvice.setDebt(true) />
											<cfelseif rs["欠费状态"][rs.currentRow] eq "已缴清">
												<cfset propAdvice.setDebt(false) />
											<cfelse>
												<p class="error">学号: <cfoutput>#rs["学号"][rs.currentRow]#</cfoutput> 欠费状态数据无法识别.</p>
										</cfif>
									
										<!--- 无数据异常的情况下保存数据 --->
										<cfif rs["欠费状态"][rs.currentRow] eq "欠费" or rs["欠费状态"][rs.currentRow] eq "已缴清">
										
                                            <cfset sql = "UPDATE t_student_status a 
															SET a.student_prop = :StudentProps 
															WHERE a.stu_id = :stuId  
															"/>
                                                            
                                            <cfset queryObj = new query( datasource=application.dnsMaster)/>
											<cfset queryObj.addParam( name="StudentProps", value=propAdvice.stringProperty() , cfsqltype="cf_sql_varchar" )/>
                                            <cfset queryObj.addParam( name="stuId", value=rs["学号"][rs.currentRow] , cfsqltype="cf_sql_varchar" )/>
                                            <cfset queryObj.execute( sql=sql )/>
										
											<cfset studentUpdate++ />
										
										</cfif>
									
									<cfelse>
									
										<p class="error">学号 <cfoutput>#rs['学号'][rs.currentRow]#</cfoutput> 姓名 <cfoutput>#rs['姓名'][rs.currentRow]#</cfoutput> 不匹配</p>
									
								</cfif>
								
								<cfelse>
									<p class="error">学号: <cfoutput>#rs["学号"][rs.currentRow]#</cfoutput> 学籍信息不正确, 请核对.</p>
							</cfif>
							
						</cfloop>
					
					</cftransaction>
				</cfif>
			</cfif>
		</cfif> 
		
		<p>更新学生欠费数据 [<cfoutput>#studentUpdate#</cfoutput>] 条.</p>
		
		<a target="_parent" href="<cfoutput>#buildURL('chargeStudent', targetArgs)#</cfoutput>">刷新欠费数据</a>
		
		<!--- 删除临时文件 --->
		<cfif fileExists(temFile)>
			<cffile action="delete" file="#temFile#" />		
		</cfif>
	
	</cfif>

</body>
</html>